Define decision tables in Excel workbooks

To author rules in Excel, you simply write rules in tables, and use Oracle Policy Modeling styles to identify the type of information in the cells so that they can be compiled for use with the Oracle Determinations Engine. You can have as many worksheets for rules in your document as you need.

What do you want to do?

Understand the styles used for rule tables

Create a rule table in Excel

Prove multiple attributes for the same set of conditions

Prove the same set of conclusions using multiple conditions

Allow rule conditions to evaluate in any order and handle missing values

Write a comparison type rule where a decision applies to a range of numbers or dates

Split rule tables according to the date they apply from

Use entity attributes in an Excel rule table

Prove a text attribute in an Excel rule

Understand the styles used for rule tables

Excel rules which are intended for compiling in Oracle Policy Modeling need to be marked up using the styles supplied with the Oracle Policy Modeling Excel document template. The following styles are used for writing rules:

Style Name	Description
Conclusion Heading	Used to mark up a conclusion column in a rule block. The text is either "conclusion" or an attribute ID.
Conclusion	Used to mark up an attribute that will be concluded by a rule
Condition Heading	Used to mark up a condition column in a rule block. The text is either "condition" or an attribute ID.
Condition	Used to mark up a condition for a part of a rule. If the condition header is "condition", the condition must be a complete expression or a valid boolean attribute. If the condition header is an attribute ID, the condition must be either a constant or a comparison of the same type as the attribute.
Else	Used to mark up the else condition
Commentary	Used to mark up descriptive text in a rule block. The text is ignored when generating the rule.

The heading cells are optional. Similarly, the order of cells is irrelevant since each style is unique - as long as the necessary styles are used with valid cell contents.

NOTES:

Regardless of the order of declaration on a worksheet, the order of processing is "global entity", "entity" and then any attributes. This ensures that attributes appear in the correct entity.
To format a cell as a currency value, do not use the button on the Excel formatting toolbar - instead go to Format | Cells and select Currency on the Number tab.
When working with numbers, currencies, dates and time in Microsoft Excel, the regional setting of the computer should accord with the rulebase project’s region. This is because Microsoft Excel formats the data types using the templates in the regional setting.
If you use a text attribute you can either put the value of that text attribute in quotes or not in quotes and it will be treated the same way.
If you want to use a text function in a rule table, you need to put the function text in parentheses.

Create a rule table in Excel

When you add an Excel document to your project, it will contain a rule template on the Rule Table worksheet that looks like this:

condition	condition	conclusion	conclusion


commentary


	else

To write a simple rule in Excel which contains a single condition and a single conclusion, follow the steps below. In this example we will be concluding the nationality of the individual based on their country of citizenship. NOTE: Variable attributes should be declared in a properties file before use in Excel. (There is no need to declare boolean attributes before using them in rules.) In this example, the text variables "the country of citizenship" and "the nationality of the individual" have already been declared in the properties files in the project.

Replace the text condition in the second column with "the country of citizenship". This cell is already in the correct Condition Heading style. As we will only be having one set of conditions you can delete the first condition column.
Replace the text conclusion with "the nationality of the individual". This cell is already in the correct Conclusion Heading style. As we will only be having one set of conclusions you can delete the other conclusion column.
Type "USA" in the cell below the "the country of citizenship" cell. Tab across to the next cell (the cell below the "the nationality of the individual" cell) and type "American". These cells are already in the correct styles: Condition and Conclusion respectively. Delete the next two rows, as they won't be used.
In the row below, enter another condition "Scotland" with the associated conclusion "Scottish". Follow this on the next row with another condition "Japan" and conclusion "Japanese".
Type "uncertain" in the cell next to the else condition. This applies an alternative conclusion of "uncertain".

Your rule table should look like this:

the country of citizenship	the nationality of the individual
USA	American
Scotland	Scottish
Japan	Japanese
else	uncertain

Decision tables written in Excel are converted into internally generated rule tables by Oracle Policy Modeling when the rules are compiled. The table above will create the following rule (xgen) in Oracle Policy Modeling. (This can be viewed in OPM by right-clicking on the rule document in the Project Explorer and selecting Open Rule Browser.)

the nationality of the individual Rule Tables.xgen
a1: the nationality of the individual
"American"	a2: the country of citizenship = "USA"
"Scottish"	a2: the country of citizenship = "Scotland"
"Japanese"	a2: the country of citizenship = "Japan"
"uncertain"	otherwise

Prove multiple attributes for the same set of conditions

Using just one table in Excel you can prove multiple attributes for the same set of conditions (unlike in Word which would require multiple rule tables).

Assuming you have the following variables already declared, the text variables "the country of citizenship", "the nationality of the individual" and "the currency of the country", you could have the following rule table:

the country of citizenship	the nationality of the individual	the currency of the country
USA	American	Dollar
Scotland	Scottish	Pound
Japan	Japanese	Yen
else	uncertain	uncertain

Prove the same set of conclusions using multiple conditions

You can specify multiple conditions for a particular conclusion in Excel, merging the conclusion cells if appropriate to influence the way the rule is evaluated.

For example, you may wish to determine the appropriate ticket type for different combinations of adults and children. If you have the following variables:

Attribute Type	Attribute Text	Legend Key
Number	the number of adults in the group	Adults
Number	the number of children in the group	Children
Text	the ticket type	Ticket

you may have the following rule table:

Adults	Children	Ticket
1	0	Single
1	1	Double
2	0	Double
2	1	Family
2	2	Family
2	3	Family
3	0	Family
	else	Combo

The rule generated for this table in Oracle Policy Modeling will look like the following:

the ticket type Multiple conclusions unmerged.xgen
ticket_type: the ticket type
"Single"	all number_adults: the number of adults in the group = 1 and number_children: the number of children in the group = 0
"Double"	all number_adults: the number of adults in the group = 1 and number_children: the number of children in the group = 1
"Double"	all number_adults: the number of adults in the group = 2 and number_children: the number of children in the group = 0
"Family"	all number_adults: the number of adults in the group = 2 and number_children: the number of children in the group = 1
"Family"	all number_adults: the number of adults in the group = 2 and number_children: the number of children in the group = 2
"Family"	all number_adults: the number of adults in the group = 2 and number_children: the number of children in the group = 3
"Family"	all number_adults: the number of adults in the group = 3 and number_children: the number of children in the group = 0
"Combo"	otherwise

We can leave a condition cell empty if we do not wish to test the value of the attribute for that conclusion cell. In our example, we may decide that two adults can enter under a Family ticket if they have any children with them, and three adults can be covered by a Family ticket regardless of whether there are children with them.

Adults	Children	Ticket
1	0	Single
1	1	Double
2	0	Double
2		Family
3		Family
	else	Combo

This will simplify the logic, and the rule generated:

the ticket type Multiple conclusions simplified.xgen
ticket_type: the ticket type
"Single"	all number_adults: the number of adults in the group = 1 and number_children: the number of children in the group = 0
"Double"	all number_adults: the number of adults in the group = 1 and number_children: the number of children in the group = 1
"Double"	all number_adults: the number of adults in the group = 2 and number_children: the number of children in the group = 0
"Family"	number_adults: the number of adults in the group = 2
"Family"	number_adults: the number of adults in the group = 3
"Combo"	otherwise

We can also merge the cells for the conclusion values, if there are multiple condition rows that prove the same conclusion.

Adults	Children	Ticket
1	0	Single
1	1	Double
2	0	Double
2		Family
3		Family
	else	Combo

This will simplify the appearance of the Excel rule table and emphasize that the value inferred for Ticket will be the same in more than one possible scenario. However, it will also change the way Oracle Policy Modeling interprets the logic of the rule. The internal rule table generated from an Excel rule table includes a row for each Excel conclusion cell. This means that instead of having two rows in the generated rule table proving the same conclusion value (which will be evaluated in order from the top down), we now have a single row proving the conclusion value, with multiple options that may be evaluated in any order. This can be useful if our rules need to allow for some condition values being unknown.

the ticket type Multiple conclusions merged.xgen
ticket_type: the ticket type
"Single"	all number_adults: the number of adults in the group = 1 and number_children: the number of children in the group = 0
"Double"	either all number_adults: the number of adults in the group = 1 and number_children: the number of children in the group = 1 or all number_adults: the number of adults in the group = 2 and number_children: the number of children in the group = 0
"Family"	either number_adults: the number of adults in the group = 2 or number_adults: the number of adults in the group = 3
"Combo"	otherwise

TIP: To see an example of a complete rulebase with merged condition and conclusion cells, open and run the Insurance Fraud Score example rulebase project provided in the Examples folder in the Oracle Policy Modeling installation folder.

Allow rule conditions to evaluate in any order and handle missing values

The internal rule tables that are generated by Oracle Policy Modeling from decision tables in Excel are evaluated row by row from the top down. If the first row of a table cannot be evaluated (ie if some of the condition values are unknown), then the evaluation of the rule table as a whole will not progress beyond that row, even if a later row in the same table can be evaluated because all of its condition values are fully known.

In some cases, this may not be the most useful way for the rule to evaluate. If a single conclusion is proved in multiple ways, you can merge a single conclusion cell across all of the different condition rows. Oracle Policy Modeling will then allow any of those condition rows to prove the conclusion value, in any order.

For example, in the following rule cells we would like either of the two rows to be able to prove the conclusion.

Occupation	Age	Entitlement
Student		TRUE
	16	TRUE

With the current rule table layout, the rule generated by Oracle Policy Modeling will have separate rows for each of the rows in our Excel rule. Because a rule table evaluates from the top down, this will mean that even if we know that a person is 16 and hence is entitled to Youth Benefit, the rule table would be unable to conclude a result until we know the person's occupation and can evaluate the first row.

the applicant is entitled to the benefit Handle missing data unmerged.xgen
applicant_entitlement: the applicant is entitled to the benefit
true	applicant_occupation: the applicant's occupation = "Student"
true	applicant_age: the applicant's age = 16
uncertain	otherwise

However, if we merge the cells containing the conclusions that apply to these two rows, the internal rule generated by Oracle Policy Modeling combines these rows with an "or" condition in a single rule table row, rather than the two separate rule table rows generated above.

Occupation	Age	Entitlement
Student		TRUE
	16	TRUE

This new structure allows the conditions proving the conclusion to be evaluated in any order, so the second row will now allow the rule to be evaluated even if the first row values are unknown.

the applicant is entitled to the benefit Handle missing data merged.xgen
applicant_entitlement: the applicant is entitled to the benefit
true	either applicant_occupation: the applicant's occupation = "Student" or applicant_age: the applicant's age = 16
uncertain	otherwise

the applicant is entitled to the benefit

Handle missing data merged.xgen

applicant_entitlement: the applicant is entitled to the benefit

true

either

applicant_occupation: the applicant's occupation = "Student" or

applicant_age: the applicant's age = 16

uncertain

otherwise

Write a comparison type rule where a decision applies to a range of numbers or dates

For non-text conditions, it is likely that the decision will apply to a range of numbers or dates rather than to a specific number or date. A simple example is the mapping of taxable income to tax rates for a particular date range:

Attribute Type	Attribute Text	Legend Key
Date	the assessment date	Assessment Date
Currency	the client's taxable income	Taxable Income
Number	the client's tax rate	Tax Rate

Assessment Date		Taxable Income		Tax Rate
>=2006-07-01	<2007-07-01	>=0	<12000	0
		>=12000	<24000	0.22
		>=24000	<36000	0.27
		>=36000	<48000	0.36
		>=48000		0.48
>=2005-07-01	<2006-07-01	>=0	<12000	0
		>=12000	<24000	0.22
		>=24000	<36000	0.27
		>=36000	<48000	0.35
		>=48000		0.47
>=2004-07-01	<2005-07-01	>=0	<12000	0
		>=12000	<24000	0.21
		>=24000	<36000	0.26
		>=36000	<48000	0.34
		>=48000		0.46
			else	0.5

It is also possible that you may want to have multiple comparisons for one attribute as exemplified below:

Attribute Type	Attribute Text	Legend Key
Number	the current temperature	Temp
Text	the person's gender	Gender
Text	the state the person is likely to be in	State

Temp	Temp	Gender	State
	<=0		Freezing
>0	<12	male	Cold
>0	<16	female	Cold
>=20	<24		Comfortable
>30			Hot
		else	Uncertain

Split rule tables according to the date they apply from

Tables can be split over several sheets in the same file to allow for regular table updates that apply from a particular date. This is managed by the insertion of a master table that prioritizes the sheets. The prioritization is done by reference to sheet name, which is specified in the tab for the sheet. For example, you could have:

Attribute Type	Attribute Text	Legend Key
Text	the type of ticket	Ticket
Currency	the ticket price	Price
Date	the date of purchase	Date

Date	Apply Sheet
>= 2006-07-01	2006-2007
>= 2005-07-01	2005-2006
else	pre 2005-2006

The logic of these tables is consolidated on compile, and therefore does not result in multiply proven attributes. Master tables use the standard rule condition and conclusion styles but have a single conclusion column headed "Apply Sheet" in the Conclusion Heading style. Note that the text "Apply Sheet" therefore cannot be used as a column heading in a standard rule table.

In this example, you would have three other worksheets which contain the rule tables below. Note that the worksheets must be titled (case-sensitive) according to the names given in the Apply Sheet column.

pre 2005-2006

Ticket	Price
Adult	14
Concession	10
Child	6
else	14

2005-2006

Ticket	Price
Adult	16
Concession	12
Child	8
else	16

2006-2007

Ticket	Price
Adult	20
Concession	15
Child	10
else	20

This will create the following rule in Oracle Policy Modeling:

the ticket price Split tables.xgen
price_ticket: the ticket price
20	all ticket_type: the type of ticket = "Adult" and purchase_date: the date of purchase >= 07/01/2006
15	all ticket_type: the type of ticket = "Concession" and purchase_date: the date of purchase >= 07/01/2006
10	all ticket_type: the type of ticket = "Child" and purchase_date: the date of purchase >= 07/01/2006
20	purchase_date: the date of purchase >= 07/01/2006
16	all ticket_type: the type of ticket = "Adult" and purchase_date: the date of purchase >= 07/01/2005
12	all ticket_type: the type of ticket = "Concession" and purchase_date: the date of purchase >= 07/01/2005
8	all ticket_type: the type of ticket = "Child" and purchase_date: the date of purchase >= 07/01/2005
16	purchase_date: the date of purchase >= 07/01/2005
14	ticket_type: the type of ticket = "Adult"
10	ticket_type: the type of ticket = "Concession"
6	ticket_type: the type of ticket = "Child"
14	true
uncertain	otherwise

TIP: To see an example of a complete rulebase using 'Apply Sheet' to reason about attributes that change over time, open and run the Insurance Fraud Score example rulebase project provided in the Examples folder in the Oracle Policy Modeling installation folder.

Use entity attributes in an Excel rule table

You can prove entity-level attributes in Excel rule tables, however, all conclusion attributes in the table must be in the same entity. The condition attributes in the rule table may be in the same entity as the conclusion, or they may reference any entities in the containment relationships of the conclusion entity.

For example, the following rule table infers conclusion attributes in "the pet" entity, using condition attributes in the entity "the child" and the global entity, which are both in its containment relationship as shown:

the grocery shopping has been done	the child is on school holidays	the pet is happy	the pet is well fed
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	TRUE	FALSE
	FALSE	FALSE	FALSE
	else	uncertain	uncertain

Entity level attributes can also be used in condition cells with most entity functions. For example, the following rule uses the InstanceCount function to set the child's pocket money depending on how many pets she owns.

condition	the amount of pocket money the child gets
the number of the child's pets = 0	$5.00
the number of the child's pets = 1	$8.00
the number of the child's pets = 2	$10.00
else	$15.00

NOTE: The entity functions that cannot be used in this way in Excel are those which deal with multiple entities: ForScope, ForAllScope, ExistsScope, IsMemberOf, IsNotMemberOf, InstanceEquals, InstanceNotEquals.

TIP: To see an example of a complete rulebase using entity level attributes, functions and calculations based on entity instances, open and run the Insurance Fraud Score example rulebase project provided in the Examples folder in the Oracle Policy Modeling installation folder.

Prove a text attribute in an Excel rule

When proving a text attribute in an Excel rule, you need to enclose the attribute text in parentheses so that the compiler recognizes it as an attribute.

For example, if you had the following declarations:

Attribute Type	Attribute Text
Text	the location of the overall winner
Text	the winner of the overall award
Text	the winner of the award in Australia
Text	the winner of the award in Japan
Text	the winner of the award in the UK
Text	the winner of the award in the US

you would need to put the text attribute’s text in parentheses when it is being concluded in a rule table. For example:

the location of the overall winner	the winner of the overall award
Australia	(the winner of the award in Australia)
Japan	(the winner of the award in Japan)
United Kingdom	(the winner of the award in the UK)
United States	(the winner of the award in the US)
else	uncertain

If you had not put the parentheses around these text attributes in the rule, these attributes would not be recognized and the resulting rule would conclude the literal strings.

When concluding a specific value for a text attribute it is not necessary to enclose it in parentheses (note that you can either put the value of that text attribute in quotes or not in quotes and it will be treated the same way).